set hive.exec.dynamic.partition=true;            --  动态分区
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=500;         --  每天生成 60 个分区
set hive.exec.max.dynamic.partitions.pernode=500; --  每天生成 60 个分区
with wide_sign as (--签收宽表基础数据
    select waybill_no                                                                                                                 --运单号
         , first_code                                                                                                                 --一段码
         , second_code                                                                                                                --二段码
         , third_code                                                                                                                 --三段码
         , ordersource_code                                                                                                           --多平台订单来源编码
         , ordersource_name                                                                                                           --多平的订单来源名称
         --任务号 AB站点
         , send_station_loading_send_later_shipment_no                                                                                --A路段始发站点最晚发车扫描任务号
         , next_station_unloading_arrival_early_shipment_no                                                                           --B路段目的站点最早卸车到件扫描对应任务号
         --包号 B站点 发件,到件
         , next_station_send_packagecode                                                                                              --B路段目的站点发件扫描包号
         , next_station_arrival_packagecode                                                                                           --B路段目的站点到件扫描包号
         --扫描员 B站点 到件
         --少了 发件 扫描员
         , next_station_arrival_scan_user_code                                                                                        --B路段目的站点到件扫描员CODE
         , next_station_arrival_scan_user                                                                                             --B路段目的站点到件扫描员
         , next_station_send_scan_user                                                                                                --B路段目的站点发件扫描员
         , next_station_send_scan_user_code                                                                                           --B路段目的站点发件扫描员CODE
         --AB站点 ,类型
         , send_station_network_code                                                                                                  --A路段始发站点
         , send_station_network_type                                                                                                  --A路段始发站点类型
         , next_station_network_code                                                                                                  --B路段目的站点
         , next_station_network_type                                                                                                  --B路段目的站点类型
         --A站点规划下一站 ,类型
         , send_station_send_next_network_code                                                                                        --A路段始发站点发件扫描下一站点
         , send_station_send_next_network_type                                                                                        --A路段始发站点发件扫描下一站点类型
         --时间 A站点 发件
         , send_station_send_later_time                                                                                               --A路段始发中心/网点最晚发件时间
         , if(next_station_arrival_early_time < date_add('{{ execution_date | cst_ds }}', -30), null,
              next_station_arrival_early_time)                               as next_send_station_arrival_early_time                  --B路段目的站点最早到件时间
         , if(next_station_send_later_time < date_add('{{ execution_date | cst_ds }}', -30), null,
              next_station_send_later_time)                                  as next_send_station_send_later_time                     --B路段目的站点最晚发件时间
         --最晚签收时间
         , sign_early_scantime                                               as sign_later_scantime                                   --最后一次签收时间
         , outstore_early_scantime                                           as outstore_later_scantime                               --出库时间
         , case
               when substr(coalesce(sign_early_scantime, '9999-01-01'), 1, 10) = '{{ execution_date | cst_ds }}' and
                    substr(coalesce(outstore_early_scantime, '9999-01-01'), 1, 10) = '{{ execution_date | cst_ds }}'
                   then least(sign_early_scantime, outstore_early_scantime)
               when substr(coalesce(sign_early_scantime, '9999-01-01'), 1, 10) = '{{ execution_date | cst_ds }}' and
                    substr(coalesce(outstore_early_scantime, '9999-01-01'), 1, 10) <> '{{ execution_date | cst_ds }}'
                   then sign_early_scantime
               when substr(coalesce(sign_later_scantime, '9999-01-01'), 1, 10) <> '{{ execution_date | cst_ds }}' and
                    substr(coalesce(outstore_early_scantime, '9999-01-01'), 1, 10) = '{{ execution_date | cst_ds }}'
                   then outstore_early_scantime
        end                                                                  as pd_sign_time                                          --签收时间判断,选择当天最早签收时间
         , lead(next_station_network_code)
                over (partition by waybill_no order by send_station_number ) as next_next_station_network_code                        --B站点下一站
         , send_station_number
         , '{{ execution_date | cst_ds }}'                                                      as date_time
         , input_time
         , lead(next_station_unloading_arrival_early_shipment_no)
                over (partition by waybill_no order by send_station_number ) as next_next_station_unloading_arrival_early_shipment_no --B站点下一站最早卸车到件扫描对应任务号
    from jms_dwd.dwd_wide_sign_trace_waybill_step_dt
    where dt = '{{ execution_date | cst_ds }}'
      and (substr(sign_early_scantime, 1, 10) = '{{ execution_date | cst_ds }}'
        or substr(outstore_early_scantime, 1, 10) = '{{ execution_date | cst_ds }}') --运单表的是否签收, 和签收时间是正常签收和出库都代表
      and (input_time is not null
        and substr(input_time, 1, 10) >= date_add('{{ execution_date | cst_ds }}', -44))
    union all
    --下面代码是始发站点即是中心的意思.则做下面替换操作,始发用null替换,目的用始发替换,
    select waybill_no                                                                                      --运单号
         , first_code                                                                                      --一段码
         , second_code                                                                                     --二段码
         , third_code                                                                                      --三段码
         , ordersource_code                                                                                --多平台订单来源编码
         , ordersource_name                                                                                --多平的订单来源名称
         --任务号 AB站点
         , null                                        as send_station_loading_send_later_shipment_no      --A路段始发站点任务号
         , send_station_loading_send_later_shipment_no as next_station_unloading_arrival_early_shipment_no --B路段目的站点最早卸车到件扫描对应任务号
         --包号 B站点 发件,到件
         , send_station_send_packagecode               as next_station_send_packagecode                    --B路段目的站点发件扫描包号
         , send_station_arrival_packagecode            as next_station_arrival_packagecode                 --B路段目的站点到件扫描包号
         --扫描员 B站点 到件
         --少了 发件 扫描员
         , send_station_arrival_scan_user_code         as next_station_arrival_scan_user_code              --B路段目的站点到件扫描员CODE
         , send_station_arrival_scan_user              as next_station_arrival_scan_user                   --B路段目的站点到件扫描员
         , send_station_send_scan_user                 as next_station_send_scan_user                      --B路段目的站点发件扫描员CODE
         , send_station_send_scan_user_code            as next_station_send_scan_user_code                 --B路段目的站点发件扫描员
         --AB站点 ,类型
         , null                                        as send_station_network_code                        --A路段始发站点
         , null                                        as send_station_network_type                        --A路段始发站点类型
         , send_station_network_code                   as next_station_network_code                        --B路段目的站点
         , send_station_network_type                   as next_station_network_type                        --B路段目的站点类型
         --A站点规划下一站 ,类型
         , null                                        as send_station_send_next_network_code              --A路段始发站点发件扫描下一站点
         , null                                        as send_station_send_next_network_type              --A路段始发站点发件扫描下一站点类型
         --时间 A站点 发件
         , null                                        as send_station_send_later_time                     --A路段始发中心/网点最晚发件时间
         --时间 B站点  到件, 发件
         , if(send_station_arrival_early_time < date_add('{{ execution_date | cst_ds }}', -30), null,
              send_station_arrival_early_time)         as next_send_station_arrival_early_time             --B路段目的站点最早到件时间
         , if(send_station_send_later_time < date_add('{{ execution_date | cst_ds }}', -30), null,
              send_station_send_later_time)            as next_send_station_send_later_time                --B路段目的站点最晚发件时间
         , sign_early_scantime                         as sign_later_scantime                              --最后一次签收时间
         , outstore_early_scantime                     as outstore_later_scantime
         , case
               when substr(coalesce(sign_early_scantime, '9999-01-01'), 1, 10) = '{{ execution_date | cst_ds }}' and
                    substr(coalesce(outstore_early_scantime, '9999-01-01'), 1, 10) = '{{ execution_date | cst_ds }}'
                   then least(sign_early_scantime, outstore_early_scantime)
               when substr(coalesce(sign_early_scantime, '9999-01-01'), 1, 10) = '{{ execution_date | cst_ds }}' and
                    substr(coalesce(outstore_early_scantime, '9999-01-01'), 1, 10) <> '{{ execution_date | cst_ds }}'
                   then sign_early_scantime
               when substr(coalesce(sign_early_scantime, '9999-01-01'), 1, 10) <> '{{ execution_date | cst_ds }}' and
                    substr(coalesce(outstore_early_scantime, '9999-01-01'), 1, 10) = '{{ execution_date | cst_ds }}'
                   then outstore_early_scantime
        end                                            as pd_sign_time                                     --签收时间判断,选择当天最早签收时间
         , next_station_network_code                   as next_next_station_network_code                   --B站点下一站
         , send_station_number
         , '{{ execution_date | cst_ds }}'                                as date_time                                        --日期
         , input_time
         , next_station_unloading_arrival_early_shipment_no
    from jms_dwd.dwd_wide_sign_trace_waybill_step_dt
    where dt = '{{ execution_date | cst_ds }}'
      and ((substr(sign_early_scantime, 1, 10) = '{{ execution_date | cst_ds }}'
        or substr(outstore_early_scantime, 1, 10) = '{{ execution_date | cst_ds }}'))
      and send_station_network_type = 2
      and send_station_number = 1
      and (input_time is not null
        and substr(input_time, 1, 10) >= date_add('{{ execution_date | cst_ds }}', -44))
),
     b_send_or_arrival as (--B是中心,且轨迹在签收之前 ,且只做了装车发件或者卸车到件中的一个
         select waybill_no                                                                             --运单号
              , first_code                                                                             --一段码
              , second_code                                                                            --二段码
              , third_code                                                                             --三段码
              , ordersource_code                                                                       --多平台订单来源编码
              , ordersource_name                                                                       --多平的订单来源名称
              --任务号 AB站点
              , send_station_loading_send_later_shipment_no                                            --A路段始发站点最晚发车扫描任务号
              , next_station_unloading_arrival_early_shipment_no                                       --B路段目的站点最早卸车到件扫描对应任务号
              --包号 B站点 发件,到件
              , next_station_send_packagecode                                                          --B路段目的站点发件扫描包号
              , next_station_arrival_packagecode                                                       --B路段目的站点到件扫描包号
              --扫描员 B站点 到件
              --少了 发件 扫描员
              , next_station_arrival_scan_user_code                                                    --B路段目的站点到件扫描员CODE
              , next_station_arrival_scan_user                                                         --B路段目的站点到件扫描员
              , next_station_send_scan_user                                                            --B路段目的站点发件扫描员
              , next_station_send_scan_user_code                                                       --B路段目的站点发件扫描员CODE
              --AB站点 ,类型
              , send_station_network_code                                                              --A路段始发站点
              , send_station_network_type                                                              --A路段始发站点类型
              , next_station_network_code                                                              --B路段目的站点
              , next_station_network_type                                                              --B路段目的站点类型
              --A站点规划下一站 ,类型
              , send_station_send_next_network_code                                                    --A路段始发站点发件扫描下一站点
              , send_station_send_next_network_type                                                    --A路段始发站点发件扫描下一站点类型
              --时间 A站点 发件
--          , send_station_send_later_time                                                                        --A路段始发中心/网点最晚发件时间
--          , next_send_station_arrival_early_time                                                                --B路段目的站点最早到件时间
--          , next_send_station_send_later_time                                                                   --B路段目的站点最晚发件时间
              , if(send_station_send_later_time > pd_sign_time, null,
                   send_station_send_later_time)               as send_station_send_later_time         --A路段始发中心/网点最晚发件时间
              --时间 B站点  到件, 发件
              , if(next_send_station_arrival_early_time > pd_sign_time, null,
                   next_send_station_arrival_early_time)       as next_send_station_arrival_early_time --B路段目的站点最早到件时间
              , if(next_send_station_send_later_time > pd_sign_time, null,
                   next_send_station_send_later_time)          as next_send_station_send_later_time    --B路段目的站点最晚发件时间
              --最晚签收时间
              , sign_later_scantime                                                                    --最后一次签收时间
              , outstore_later_scantime                                                                --出库时间
              , pd_sign_time                                                                           --签收时间判断,选择当天最早签收时间
              , next_next_station_network_code                                                         --B站点下一站
              , send_station_number
              , date_time
              , case when input_time is null then 1 else 0 end as before_30_input                      --1代表超过30天
              , input_time                                                                             --录入时间
--               , next_next_ship_sendtime_user_usercode
              , next_next_station_unloading_arrival_early_shipment_no
         from wide_sign
         where next_station_network_type = 2 --中心
     ),
     other_base as (
         select shipment_no
              , scan_site_code
              , scan_time
              , scan_user_code
              , scan_user
         from (
                  select shipment_no
                       , scan_site_code
                       , scan_time
                       , scan_user_code
                       , scan_user
                       , row_number() over (partition by shipment_no,scan_site_code order by scan_time desc ) as rn
                  from jms_dwd.dwd_tab_barscan_unloading_base_dt
                  where dt >= date_add('{{ execution_date | cst_ds }}', -30)
                    and dt <= '{{ execution_date | cst_ds }}'
                    and shipment_no is not null
              ) a
         where rn = 1
     ),
     send_base as (
         select scan_site_code, next_station_code, waybill_no, scan_time
         from jms_dwd.dwd_tab_barscan_send_base_dt
         where dt <= '{{ execution_date | cst_ds }}'
           and dt >= date_add('{{ execution_date | cst_ds }}', -30)
           and next_network_type = 2
         group by scan_site_code, next_station_code, waybill_no, scan_time
     ),
     shipment_table as (
         select shipment_no
              , max(shipment_name) as shipment_name
         from jms_dwd.dwd_tmsnew_shipment_union_base_dt
         where dt >= date_add('{{ execution_date | cst_ds }}', -30)
           and dt <= '{{ execution_date | cst_ds }}'
         group by shipment_no
     ),
     master_date as (-- 因扫描件:运单的签收之前的轨迹中存在装车发件操作对应的下一站是本中心的
         select b.waybill_no                                                                           --运单号
              , b.first_code                                                                           --一段码
              , b.second_code                                                                          --二段码
              , concat(b.first_code, ',', b.second_code, ',', b.third_code) as third_code              --三段码
              , b.ordersource_code                                                                     --多平台订单来源编码
              , b.ordersource_name                                                                     --多平的订单来源名称
              , b.send_station_loading_send_later_shipment_no                                          --A路段始发站点任务号
              , b.next_station_unloading_arrival_early_shipment_no                                     --B路段目的站点最早卸车到件扫描对应任务号
              , b.next_station_send_packagecode                                                        --B路段目的站点发件扫描包号
              , b.next_station_arrival_packagecode                                                     --B路段目的站点到件扫描包号
              , b.next_station_arrival_scan_user_code                                                  --B路段目的站点到件扫描员CODE
              , b.next_station_arrival_scan_user                                                       --B路段目的站点到件扫描员
              , b.next_station_send_scan_user                                                          --B路段目的站点发件扫描员CODE
              , b.next_station_send_scan_user_code                                                     --B路段目的站点发件扫描员
              , b.send_station_network_code                                                            --A路段始发站点
              , b.send_station_network_type                                                            --A路段始发站点类型
              , b.next_station_network_code                                                            --B路段目的站点
              , b.next_station_network_type                                                            --B路段目的站点类型
              , b.send_station_send_next_network_code                                                  --A路段始发站点发件扫描下一站点
              , b.send_station_send_next_network_type                                                  --A路段始发站点发件扫描下一站点类型
              , b.send_station_send_later_time                                                         --A路段始发中心/网点最晚发件时间
              , b.next_send_station_arrival_early_time                                                 --B路段目的站点最早到件时间
              , b.next_send_station_send_later_time                                                    --B路段目的站点最晚发件时间
              , b.sign_later_scantime                                                                  --最后一次签收时间
              , b.outstore_later_scantime                                                              --最后一次出库时间
              , b.date_time                                                                            --日期
              , b.next_next_station_network_code                                                       --B站点下一站
              , case
                    when b.next_send_station_arrival_early_time is not null and
                         b.next_send_station_send_later_time is null then 1
                    else 0 end                                              as have_arr_no_send_flag   --1代表有到未发标识
              , case
                    when b.next_send_station_send_later_time is not null and
                         b.next_send_station_arrival_early_time is null then 1
                    else 0 end                                              as have_send_no_arr_flag   --1代表有发未到标识
              , case
                    when b.next_send_station_arrival_early_time is null and
                         b.next_send_station_send_later_time is null then 1
                    else 0 end                                              as no_arr_no_send_flag     --1代表无到无发标识
              , case
                    when b.next_send_station_arrival_early_time is not null and
                         b.next_send_station_send_later_time is not null then 1
                    else 0 end                                              as have_arr_have_send_flag --1代表有到有发标识
              , b.before_30_input                                                                      --1代表超过30天
              , b.input_time                                                                           --录入时间
              , next_next_station_unloading_arrival_early_shipment_no
         from b_send_or_arrival b
                  join send_base a
                       on b.waybill_no = a.waybill_no
                           and b.next_station_network_code = a.next_station_code
                           and b.pd_sign_time > a.scan_time
     ),
     send_or_arrival as (--(有发未到)or(有到未发)
         select waybill_no                                                     as waybill_no                                       --运单号
              , next_station_network_code                                      as next_station_network_code                        --B路段目的站点
              , min(third_code)                                                as third_code                                       --三段码
              , min(ordersource_code)                                          as ordersource_code                                 --多平台订单来源编码
              , min(ordersource_name)                                          as ordersource_name                                 --多平台订单来源编码
              , first_value(send_station_loading_send_later_shipment_no, true) as send_station_loading_send_later_shipment_no      --A路段始发站点任务号
              , first_value(next_station_unloading_arrival_early_shipment_no,
                            true)                                              as next_station_unloading_arrival_early_shipment_no --B路段目的站点最早卸车到件扫描对应任务号
              , min(next_station_send_packagecode)                             as next_station_send_packagecode                    --B路段目的站点发件扫描包号
              , max(next_station_arrival_packagecode)                          as next_station_arrival_packagecode                 --B路段目的站点到件扫描包号
              , min(next_station_arrival_scan_user_code)                       as next_station_arrival_scan_user_code              --B路段目的站点到件扫描员CODE
              , min(next_station_arrival_scan_user)                            as next_station_arrival_scan_user                   --B路段目的站点到件扫描员
              , max(next_station_send_scan_user)                               as next_station_send_scan_user                      --B路段目的站点发件扫描员CODE
              , max(next_station_send_scan_user_code)                          as next_station_send_scan_user_code                 --B路段目的站点发件扫描员
              , max(send_station_network_code)                                 as send_station_network_code                        --A路段始发站点
              , max(send_station_network_type)                                 as send_station_network_type                        --A路段始发站点类型
              , max(next_station_network_type)                                 as next_station_network_type                        --B路段目的站点类型
              , max(send_station_send_next_network_code)                       as send_station_send_next_network_code              --A路段始发站点发件扫描下一站点
              , max(send_station_send_next_network_type)                       as send_station_send_next_network_type              --A路段始发站点发件扫描下一站点类型
              , max(send_station_send_later_time)                              as send_station_send_later_time                     --A路段始发中心/网点最晚发件时间
              , min(next_send_station_arrival_early_time)                      as next_station_arrival_early_time                  --B路段目的站点最早到件时间
              , min(next_send_station_send_later_time)                         as next_station_send_later_time                     --B路段目的站点最晚发件时间
              , max(sign_later_scantime)                                       as sign_later_scantime                              --最后一次签收时间
              , max(outstore_later_scantime)                                   as outstore_later_scantime                          --最后一次出库时间
              , max(date_time)                                                 as date_time                                        --日期
              , max(next_next_station_network_code)                            as next_next_station_network_code                   --B站点下一站
              , max(have_arr_no_send_flag)                                     as have_arr_no_send_flag                            --1代表有到未发
              , max(have_send_no_arr_flag)                                     as have_send_no_arr_flag                            --1代表有到未发
              , max(no_arr_no_send_flag)                                       as no_arr_no_send_flag                              --1代表未到未发
              , max(have_arr_have_send_flag)                                   as have_arr_have_send_flag                          --1代表有到有发
              , max(before_30_input)                                           as before_30_input                                  --1代表超过30天
              , max(input_time)                                                as input_time                                       --录入时间
              , max(next_next_station_unloading_arrival_early_shipment_no)     as next_next_station_unloading_arrival_early_shipment_no
         from master_date a
--          where a.have_arr_have_send_flag <> 1 --1代表有到有发
         where a.no_arr_no_send_flag <> 1
         group by waybill_no
                , next_station_network_code
     )
insert
overwrite
table
jms_dm.dm_tab_cn_center_scan_lack_total_dt
partition
(
dt
)
select t1.waybill_no                                                                                                       --运单号
     , t1.next_station_network_code                                                                                        --B路段目的站点
     , t1.third_code                                                                                                       --三段码
     , t1.ordersource_code                                                                                                 --多平台订单来源编码
     , t1.ordersource_name                                                                                                 --多平台订单来源编码
     , t1.send_station_loading_send_later_shipment_no                                                                      --A路段始发站点任务号
     , t1.next_station_unloading_arrival_early_shipment_no                                                                 --B路段目的站点最早卸车到件扫描对应任务号
     , t1.next_station_send_packagecode                                                                                    --B路段目的站点发件扫描包号
     , t1.next_station_arrival_packagecode                                                                                 --B路段目的站点到件扫描包号
     , t1.next_station_arrival_scan_user_code                                                                              --B路段目的站点到件扫描员CODE
     , t1.next_station_arrival_scan_user                                                                                   --B路段目的站点到件扫描员
     , t1.next_station_send_scan_user                                                                                      --B路段目的站点发件扫描员CODE
     , t1.next_station_send_scan_user_code                                                                                 --B路段目的站点发件扫描员
     , t1.send_station_network_code                                                                                        --A路段始发站点
     , t1.send_station_network_type                                                                                        --A路段始发站点类型
     , t1.next_station_network_type                                                                                        --B路段目的站点类型
     , t1.send_station_send_next_network_code                                                                              --A路段始发站点发件扫描下一站点
     , t1.send_station_send_next_network_type                                                                              --A路段始发站点发件扫描下一站点类型
     , t1.send_station_send_later_time                                                                                     --A路段始发中心/网点最晚发件时间
     , t1.next_station_arrival_early_time                                                                                  --B路段目的站点最早到件时间
     , t1.next_station_send_later_time                                                                                     --B路段目的站点最晚发件时间
     , t1.sign_later_scantime                                                                                              --最后一次签收时间
     , t1.outstore_later_scantime                                                                                          --最后一次出库时间
     , t1.date_time                                                                                                        --日期
     , t1.next_next_station_network_code                                                                                   --B站点下一站
     , t1.have_arr_no_send_flag                                                                                            --1代表有到未发
     , t1.have_send_no_arr_flag                                                                                            --1代表有发未到
     , t1.no_arr_no_send_flag                                                                                              --1代表未到未发
     , t1.have_arr_have_send_flag                                                                                          --1代表有到有发
     , t1.before_30_input                                                                                                  --1代表超过30天
     , t1.input_time                                                                                                       --录入时间
     , t2.scan_user_code                                        as other_scan_user_code                                    --到解车扫描雇员code
     , t2.scan_user                                             as other_scan_user                                         --到解车扫描雇员name
     , t2.scan_time                                             as unload_scan_time                                        --到解车扫描时间
     , t3.shipment_name                                         as next_send_station_unloading_arrival_early_shipment_name --本站卸车任务号
     , t4.shipment_name                                         as send_station_loading_send_later_shipment_name           --上一站装车任务号
     , t1.next_next_station_unloading_arrival_early_shipment_no as next_next_station_unloading_arrival_early_shipment_no
     , date_time                                                as dt
from send_or_arrival t1
         left join other_base t2
                   on t1.send_station_loading_send_later_shipment_no = t2.shipment_no
                       and t1.next_station_network_code = t2.scan_site_code
         left join shipment_table t3
                   on t1.next_station_unloading_arrival_early_shipment_no = t3.shipment_no
         left join shipment_table t4
                   on t1.send_station_loading_send_later_shipment_no = t4.shipment_no
    distribute by dt , pmod(hash(waybill_no), 60);
